--丢弃对象
DROP SEQUENCE EMAIL_USERS_PK_SEQ;
DROP TABLE EMAIL_USERS;

--创建邮件用户表
CREATE TABLE EMAIL_USERS(
	IDENTIFIER INT PRIMARY KEY,
	USER_NAME VARCHAR2(40) NOT NULL,
	AGE INTEGER,
	EMAIL VARCHAR2(35) NOT NULL CONSTRAINT EMAIL_UNI UNIQUE,
	REGISTRATION_TIME DATE NOT NULL
);

--创建序列
CREATE SEQUENCE EMAIL_USERS_PK_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

--创建触发器
CREATE TRIGGER UPDATE_EMAIL_USERS_PK_TRI BEFORE
INSERT ON EMAIL_USERS FOR EACH ROW WHEN(NEW.IDENTIFIER IS NULL)
BEGIN
  SELECT EMAIL_USERS_PK_SEQ.NEXTVAL INTO:NEW.IDENTIFIER FROM DUAL;
END;

INSERT ALL
INTO EMAIL_USERS VALUES(
NULL,
DBMS_RANDOM.STRING('X',DBMS_RANDOM.VALUE(1,40)),
DBMS_RANDOM.VALUE(0,60),
DBMS_RANDOM.STRING('X',DBMS_RANDOM.VALUE(1,35)),
TO_DATE(TRUNC(DBMS_RANDOM.VALUE(
TO_NUMBER(TO_CHAR(TO_DATE('2000-1-1','YYYY-MM-DD'),'J')),
TO_NUMBER(TO_CHAR(SYSDATE,'J')))),
'J')
)
SELECT * FROM DUAL;
